﻿CREATE PROCEDURE [maint].[GetDiffBackupExpireDate]
@DbName [sysname], @ExpireDate DATETIME OUTPUT, @CheckPointDate DATETIME=NULL, @OutputDiagnostics BIT=0
AS
BEGIN
	SET NOCOUNT ON

	DECLARE @RC int, @ERROR int
	SET @RC = 0

	IF(@OutputDiagnostics = 1)
		BEGIN
			PRINT OBJECT_NAME(@@PROCID)
			PRINT N'	@DbName = N''' + ISNULL(@DbName,N'NULL') + ''''
			PRINT N'	@CheckPointDate = N''' + ISNULL(CAST(@CheckPointDate AS nvarchar),N'NULL') + ''''
		END

	IF(@CheckPointDate IS NULL)
		SET @CheckPointDate = GETDATE()

	DECLARE @WeekDay int
	SET DATEFIRST 1
	SELECT @WeekDay = DATEPART(dw, @CheckPointDate)

	DECLARE @DiffBackupSchedule int
	SET @DiffBackupSchedule = maint.GetDiffBackupSchedule(@DbName)

	DECLARE @DiffBackupTime int
	SET @DiffBackupTime = maint.GetDiffBackupTime(@DbName)

	DECLARE @DiffBackupFrequency int
	SET @DiffBackupFrequency = maint.GetDiffBackupFrequency(@DbName)
	IF(@DiffBackupFrequency = 0)
		RAISERROR('Differential backup frequency can not be 0 if a schedule is defined.',16,1);

	DECLARE @StartDate datetime
	SET @StartDate = util.ChangeDateTime(@CheckPointDate,@DiffBackupTime)

	SET @ExpireDate = @StartDate
	WHILE (@ExpireDate < @CheckPointDate)
		OR((@DiffBackupSchedule & POWER(2,DATEPART(dw,@ExpireDate)-1)) = 0)
		BEGIN
			SET @ExpireDate = DATEADD(Hour,@DiffBackupFrequency,@ExpireDate)

			--reset for next day frequency (to account for uneven frequencies)
			IF(DATEDIFF(day,@StartDate, @ExpireDate) > 0)
				BEGIN
					SET @StartDate = DATEADD(day,1,@StartDate)
					SET @ExpireDate = @StartDate
				END
		END

	IF(@OutputDiagnostics = 1)
					PRINT N'	@ExpireDate = N''' + ISNULL(CAST(@ExpireDate AS nvarchar),N'NULL') + ''''

	RETURN(0)
END